﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Odbc;

/// <summary>
/// Summary description for IProfileDAO
/// </summary>
public class MySQLDestinationDAOImpl : AbstractDAO, IDestinationDAO {
	public MySQLDestinationDAOImpl(AbstractDAOFactory factory) : base(factory) { }

	public void saveDestination(Destination destination) {
		if (destination.Id > 0) {
			this.updateDestination(destination);
		} else {
			this.createDestination(destination);
		}
	}

	protected void createDestination(Destination destination) {
		StringBuilder sql = new StringBuilder();
		sql.Append("INSERT INTO Destination (name, zip, global, User_ID)");
		sql.Append(" VALUES (?, ?, ?, ?)");

		try {
			using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
				conn.Open();

				OdbcCommand cmd = conn.CreateCommand();
				cmd.CommandText = sql.ToString();
				cmd.Parameters.Add(new OdbcParameter("@name", destination.Name));
				cmd.Parameters.Add(new OdbcParameter("@zip", destination.Zip));
				cmd.Parameters.Add(new OdbcParameter("@global", destination.Global));
				cmd.Parameters.Add(new OdbcParameter("@User_ID", destination.User.Id));
				cmd.ExecuteNonQuery();
				cmd.Dispose();

				//Query updated ID
				OdbcCommand idCmd = conn.CreateCommand();
				idCmd.CommandText = "SELECT @@IDENTITY";
				destination.Id = (int) idCmd.ExecuteScalar();
			}
		} catch (Exception e) {
			throw new PersistenceException("Failed to create destination " + destination.Name, e);
		}
	}

	protected void updateDestination(Destination destination) {
		StringBuilder sql = new StringBuilder();
		sql.Append("UPDATE Destination SET name=?, zip=?, global=?, User_ID=?");
		sql.Append(" WHERE ID=?");

		try {
			using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
				conn.Open();

				OdbcCommand cmd = conn.CreateCommand();
				cmd.CommandText = sql.ToString();
				cmd.Parameters.Add(new OdbcParameter("@name", destination.Name));
				cmd.Parameters.Add(new OdbcParameter("@zip", destination.Zip));
				cmd.Parameters.Add(new OdbcParameter("@global", destination.Global));
				cmd.Parameters.Add(new OdbcParameter("@User_ID", destination.User.Id));
				cmd.Parameters.Add(new OdbcParameter("@ID", destination.Id));
				cmd.ExecuteNonQuery();
				cmd.Dispose();
			}
		} catch (Exception e) {
			throw new PersistenceException("Failed to update destination " + destination.Name, e);
		}
	}

	public void deleteDestination(Destination destination) {
		StringBuilder sql = new StringBuilder();
		sql.Append("DELETE FROM Destination");
		sql.Append(" WHERE ID=@ID");

		try {
			using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
				conn.Open();

				OdbcCommand cmd = conn.CreateCommand();
				cmd.CommandText = sql.ToString();
				cmd.Parameters.Add(new OdbcParameter("@ID", destination.Id));
				cmd.ExecuteNonQuery();
				cmd.Dispose();
			}
		} catch (Exception e) {
			throw new PersistenceException("Failed to delete destination " + destination.Name, e);
		}
	}

	public List<Destination> readDestinations(DestinationSearchCriteria criteria) {
		List<Destination> result = new List<Destination>();

		StringBuilder sql_select = new StringBuilder();
		StringBuilder sql_from = new StringBuilder();
		StringBuilder sql_where = new StringBuilder();

		sql_select.Append("SELECT ID, name, zip, global, User_ID");

		sql_from.Append(" FROM Destination");
		sql_where.Append(" WHERE 0=0");

		try {
			using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
				conn.Open();

				OdbcCommand cmd = conn.CreateCommand();

				if (criteria.Global != null) {
					if (criteria.Global.Value) {
						sql_where.Append(" AND global=true");
					} else {
						sql_where.Append(" AND global<>true");
					}
				}

				if ((criteria.Users != null) && (criteria.Users.Count > 0)) {
					sql_where.Append(" AND User_ID IN (");
					int i = 0;
					foreach (User user in criteria.Users.Values) {
						sql_where.Append(user.Id);
						if (i < (criteria.Users.Count - 1)) {
							sql_where.Append(", ");
						}
						i++;
					}
					sql_where.Append(")");
				}

				cmd.CommandText = sql_select.ToString() + sql_from.ToString() + sql_where.ToString();

				OdbcDataReader rs = cmd.ExecuteReader();
				while (rs.Read()) {
					Destination destination = new Destination();

					destination.Id = (int) rs["ID"];
					destination.Name = (string) rs["name"];
					destination.Zip = (int) rs["zip"];
					destination.Global = (bool) rs["global"];

					if ((criteria.Users != null) && (criteria.Users.Count > 0)) {
						User user = criteria.Users[(int) rs["User_ID"]];
						if (user != null) {
							user.Destinations.Add(destination);
							destination.User = user;
						}
					}

					result.Add(destination);
				}
				rs.Close();
			}
		} catch (Exception e) {
			throw new PersistenceException("Failed to read destinations", e);
		}

		return result;
	}
}
